<?php

function calculateCostPerProject($date) {

	include('dataBaseConnection.php');
	include_once('getDateInformation.php');
	$lastMonthEndingDay = date("Y-m-d", strtotime(getLastMonthEndingDay($date)));
	$lastMonthBeginingDay = date("Y-m-d", strtotime(getLastMonthBeginingDate($date)));

	
	//cargo los usuarios
	$query = "SELECT user FROM users"; 
	$result = mysqli_query($connection, $query); 
	$i = 0;
	while ($row = mysqli_fetch_array($result)) {
		$users[$i] = $row['user'];
		//echo $users[$i];
	  $i++;             
	}	
	
	
	//cargo los sueldos
	for ($i = 0; $i < count($users); $i++){
		$query = "SELECT salary FROM users WHERE user = '$users[$i]'";
		$result = mysqli_query($connection, $query); 
		$row = mysqli_fetch_array($result);
		$salaries[$users[$i]] = $row['salary'];
		//echo $salaries[$users[$i]];
	}
	
	//calculo el costo
	for ($i = 0; $i < count($users); $i++){
		$costs[$users[$i]] = round($salaries[$users[$i]] * 13 / 12 * 1.3);
		//echo $costs[$users[$i]];
	}
	
	//calculo el costo por hora
	for ($i = 0; $i < count($users); $i++){
		$hourCosts[$users[$i]] = round($costs[$users[$i]] /160);
		//echo $hourCosts[$users[$i]];
	}
	
	//cargo los proyectos
	$query = "SELECT code, name FROM projects"; 
	$result = mysqli_query($connection, $query); 
	$i = 0;
	while ($row = mysqli_fetch_array($result)) {
		$projects[$i] = $row['code'];
		$projectsNames[$i] = $row['name'];
		//echo $projects[$i];
	  $i++;             
	}	
	
	//cargo las consultorias
	$query = "SELECT code, name FROM consultancies"; 
	$result = mysqli_query($connection, $query); 
	$i = 0;
	while ($row = mysqli_fetch_array($result)) {
		$consultancies[$i] = $row['code'];
		$consultanciesNames[$i] = $row['name'];
		//echo $consultancies[$i];
	  $i++;             
	}	
	
	//cago las horas trabajadas por usuario por cada proyecto
	for ($i = 0; $i < count($users); $i++){
		$query = "SELECT hours, project FROM usersworkedtime WHERE user = '$users[$i]' AND date BETWEEN 'getLastMonthBeginingDate' AND '$lastMonthEndingDay'"; 
		$result = mysqli_query($connection, $query); 
		$cnt = 0;
		while ($row = mysqli_fetch_array($result)) {
			if (isset($hoursPerUserPerProject[$users[$i]][$row['project']])) {
				$hoursPerUserPerProject[$users[$i]][$row['project']] += $row['hours'];
			}else{
				$hoursPerUserPerProject[$users[$i]][$row['project']] = $row['hours'];
			}
				
			//echo $hoursPerUserPerProject[$users[$i]][$row['project']] . " ";
		  $cnt++;             
		}
	}
	
	//cago las horas trabajadas por usuario por cada consultoria
	for ($i = 0; $i < count($users); $i++){
		$query = "SELECT hours, consultancy FROM usersconsultanciestime WHERE user = '$users[$i]' AND date BETWEEN 'getLastMonthBeginingDate' AND '$lastMonthEndingDay'"; 
		$result = mysqli_query($connection, $query); 
		$cnt = 0;
		while ($row = mysqli_fetch_array($result)) {
			if (isset($hoursPerUserPerConsultancy[$users[$i]][$row['consultancy']])) {
				$hoursPerUserPerConsultancy[$users[$i]][$row['consultancy']] += $row['hours'];
			}else{
				$hoursPerUserPerConsultancy[$users[$i]][$row['consultancy']] = $row['hours'];
			}
				
			//echo $hoursPerUserPerConsultancy[$users[$i]][$row['consultancy ']] . " ";
		  $cnt++;             
		}
	}
	
	//calculo los costos de cada proyecto
		for ($i = 0; $i < count($users); $i++){
			for ($j = 0; $j < count($projects); $j++){
				if (isset($projectsCosts[$projects[$j]])) {
					if (isset($hoursPerUserPerProject[$users[$i]][$projects[$j]])) {
						$projectsCosts[$projects[$j]] += $hourCosts[$users[$i]] * $hoursPerUserPerProject[$users[$i]][$projects[$j]];
					}
				}else{
					if (isset($hoursPerUserPerProject[$users[$i]][$projects[$j]])) {
						$projectsCosts[$projects[$j]] = $hourCosts[$users[$i]] * $hoursPerUserPerProject[$users[$i]][$projects[$j]];
					}	
				}
				
			}
				
		}
	
	//Coloco en 0 aquellos projectos sin horas cargadas 	
		foreach ($projects as $project) {
			if (!isset($projectsCosts[$project])){
				$projectsCosts[$project] = 0;	
			}
		}
	
	//calculo los costos de cada consultoria
		for ($i = 0; $i < count($users); $i++){
			for ($j = 0; $j < count($consultancies); $j++){
				if (isset($consultanciesCosts[$consultancies[$j]])) {
					if (isset($hoursPerUserPerConsultancy[$users[$i]][$consultancies[$j]])) {
						$consultanciesCosts[$consultancies[$j]] += $hourCosts[$users[$i]] * $hoursPerUserPerConsultancy[$users[$i]][$consultancies[$j]];
					}
				}else{
					if (isset($hoursPerUserPerConsultancy[$users[$i]][$consultancies[$j]])) {
						$consultanciesCosts[$consultancies[$j]] = $hourCosts[$users[$i]] * $hoursPerUserPerConsultancy[$users[$i]][$consultancies[$j]];
					}	
				}
				
			}
				
		}
	
	//Coloco en 0 aquellos projectos sin horas cargadas 	
		foreach ($consultancies as $consultancy) {
			if (!isset($consultanciesCosts[$consultancy])){
				$consultanciesCosts[$consultancy] = 0;	
			}
		}
		
	//calculo la cantidad de usuarios por proyecto
	for ($i = 0; $i < count($projects); $i++){
		$projectMembers[$projects[$i]] = 0;
	}
	for ($i = 0; $i < count($users); $i++){
		for ($j = 0; $j < count($projects); $j++){
			if (isset($hoursPerUserPerProject[$users[$i]][$projects[$j]]) && $hoursPerUserPerProject[$users[$i]][$projects[$j]] > 0) {
				$projectMembers[$projects[$j]]++;
			}
		}
	}

//calculo la cantidad de usuarios por consultoria
	for ($i = 0; $i < count($consultancies); $i++){
		$consultancyMembers[$consultancies[$i]] = 0;
	}
	for ($i = 0; $i < count($users); $i++){
		for ($j = 0; $j < count($consultancies); $j++){
			if (isset($hoursPerUserPerConsultancy[$users[$i]][$consultancies[$j]]) && $hoursPerUserPerConsultancy[$users[$i]][$consultancies[$j]] > 0) {
				$consultancyMembers[$consultancies[$j]]++;
			}
		}
	}
					
	return array($projects, $projectsNames, $consultancies, $consultanciesNames, $projectsCosts, $consultanciesCosts, $projectMembers, $consultancyMembers);

}

?>